package algorithm.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import algorithm.model.vo.Record;
import algorithm.util.DBUtil;

public class RecordDao {

	public ArrayList<Record> list() throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = " select * FROM RECORDS ";
		ArrayList<Record> records = new ArrayList<Record>();

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);

			rs = stmt.executeQuery();

			while (rs.next()) {
				records.add(new Record(rs.getString("id"), rs.getInt("num"), rs
						.getString("startTime"), rs.getString("endTime"), rs
						.getInt("tryCount")));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(rs);
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
		return records;
	}

	public ArrayList<Record> search(String id) throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		ArrayList<Record> records = new ArrayList<Record>();
		String sql = " select id, num, to_char(startTime, 'HH24:MI') startTime, "
				+ " to_char(endTime, 'HH24:MI') endTime, tryCount FROM RECORDS "
				+ " where id=? order by num";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, id);

			rs = stmt.executeQuery();

			while (rs.next()) {
				records.add(new Record(rs.getString("id"), rs.getInt("num"), rs
						.getString("startTime"), rs.getString("endTime"), rs
						.getInt("tryCount")));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(rs);
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
		return records;
	}

	public Record search(String id, int num) throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = " select * FROM RECORDS where id=? and num=? ";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, id);
			stmt.setInt(2, num);

			rs = stmt.executeQuery();

			if (rs.next()) {
				return new Record(rs.getString("id"), rs.getInt("num"),
						rs.getString("startTime"), rs.getString("endTime"),
						rs.getInt("tryCount"));
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(rs);
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
	}

	public void add(String id, int num) throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		String sql = " insert into RECORDS(id, num, startTime, tryCount) "
				+ " values(?, ?, sysdate, 0) ";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, id);
			stmt.setInt(2, num);

			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
	}

	public void update(String id, int num) throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		String sql = " update RECORDS set endTime=sysdate where id=? and num=? ";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, id);
			stmt.setInt(2, num);

			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
	}

	public int totalTime(String id, int num) throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = " select trunc(((((endTime - startTime) "
				+ " - trunc(endTime - startTime)) * 24) "
				+ " - trunc(((endTime - startTime) "
				+ " - trunc(endTime - startTime)) * 24)) * 60) totalTime "
				+ " from ( "
				+ " select to_date(startTime, 'yyyymmdd hh24:mi') startTime, "
				+ " to_date(endTime, 'yyyymmdd hh24:mi') endTime "
				+ " from RECORDS where id=? and num=? ) ";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, id);
			stmt.setInt(2, num);

			rs = stmt.executeQuery();

			if (rs.next()) {
				return rs.getInt("totalTime");
			} else {
				return 0;
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(rs);
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
	}

	public Record loadInfo(String id, int num) throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		String sql = " select to_char(startTime, 'HH24MI') startTime, tryCount "
				+ " FROM RECORDS where id=? and num=? ";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, id);
			stmt.setInt(2, num);

			rs = stmt.executeQuery();

			if (rs.next()) {
				return new Record(rs.getString("startTime"),
						rs.getInt("tryCount"));
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(rs);
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
	}

	public void plusCount(String id, int num) throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		String sql = " update RECORDS set tryCount = tryCount+1 "
				+ " where id=? and num=? ";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, id);
			stmt.setInt(2, num);

			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
	}

	public void adminReset() throws Exception {
		Connection con = null;
		PreparedStatement stmt = null;
		String sql = " delete from RECORDS where id = 'admin' ";

		try {
			con = DBUtil.getConnection();
			stmt = con.prepareStatement(sql);

			stmt.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			DBUtil.close(stmt);
			DBUtil.close(con);
		}
	}

}
